package com.ruoyi.tongji.run;

import com.ruoyi.tongji.entity.宣传员;
import com.ruoyi.tongji.entity.宣传员费用登记表;
import com.ruoyi.tongji.entity.路费差额统计;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.JxlsUtils;
import com.ruoyi.tongji.utils.PlatformException;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class LufeiTongjiBeetlHebing {

    public static Date dateRangeStart = DateUtil.getDate("2022-09-11");
    public static Date dateRangeEnd = DateUtil.getDate("2022-09-20");
    public static  String dateRange = DateUtil.format(dateRangeStart, "yyyy年MM月dd日") + "至" + DateUtil.format(dateRangeEnd, "yyyy年MM月dd日");
    public static String root = "E:\\文档\\第三方费用统计\\管理费\\" + dateRange;

    public static void main(String[] args) {
        SQLManager sqlManager = ManagerFactory.jiangzhanSqlManager();




        String[] xcyType = new String[] {"正常宣传员","新宣传员"};

        for (String type : xcyType) {

            File rootFile = new File(root);
            rootFile.mkdirs();
            //if(true) return ;
            宣传员 xcy = new 宣传员();
            xcy.setType(type);
            List xcyList = sqlManager.template(xcy);
            List xcyHuizongList = new ArrayList();
            Double jianyiqiTotal = 0D;
            Double chaobannianTotal = 0D;
            Double buchongjieshaofeiTotal = 0D;
            Double qitafeiyongTotal = 0D;
            Double guanlifeiHejiTotal = 0D;

            for (Iterator<宣传员> iterator = xcyList.iterator(); iterator.hasNext();) {


                宣传员 x = iterator.next();

                //未有合并的宣传员
                if (x.getHebingName() == null) {
                    Map xcyHuizong = new HashMap();

                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    model.put("dateRange",dateRange);
                    model = 获取宣传员统计费用(name,model, sqlManager);

                    String excelTemplate = "excelTemplates/管理费费用统计.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + name + ".xls");
                        f.getParentFile().mkdirs();
                        FileOutputStream os = new FileOutputStream(f);
                        JxlsUtils.exportExcel(is,os,model);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    Double jianyiqi = model.get("jianyiqi") != null ? ((宣传员费用登记表)model.get("jianyiqi")).get总金额() : 0D;
                    Double chaobannian = model.get("chaobannian") != null ? ((宣传员费用登记表)model.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei = model.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model.get("buchongjieshaofei")).get总金额() : 0D;
                    Double qitafeiyong = model.get("qitafeiyong") != null ? ((宣传员费用登记表)model.get("qitafeiyong")).get总金额() : 0D;
                    Double guanlifeiHeji = (Double)model.get("guanlifeiHeji");

                    if (StringUtils.equals(name,"曹建涛")) {
                        name = "曹桂勇";
                    }
                    if (StringUtils.equals(name,"张秀春")) {
                        name = "岳良全";
                    }

                    xcyHuizong.put("name",name);
                    xcyHuizong.put("jianyiqi",jianyiqi);jianyiqiTotal = jianyiqiTotal + jianyiqi;
                    xcyHuizong.put("chaobannian",chaobannian);chaobannianTotal = chaobannianTotal + chaobannian;
                    xcyHuizong.put("buchongjieshaofei",buchongjieshaofei);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei;
                    xcyHuizong.put("qitafeiyong",qitafeiyong);qitafeiyongTotal = qitafeiyongTotal + qitafeiyong;
                    xcyHuizong.put("guanlifeiHeji",guanlifeiHeji);guanlifeiHejiTotal = guanlifeiHejiTotal + guanlifeiHeji;

                    xcyHuizong.put("heji",jianyiqi + chaobannian + buchongjieshaofei + guanlifeiHeji + qitafeiyong);
                    xcyHuizongList.add(xcyHuizong);
                }

                //有合并的宣传员
                if (x.getHebingName() != null) {
                    Map xcyHuizong = new HashMap();
                    Map data = new HashMap();
                    //宣传员本身
                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    data.put("dateRange",dateRange);
                    data.put("nowdate", new Date());

                    model = 获取宣传员统计费用(name,model, sqlManager);

                    Double jianyiqi = model.get("jianyiqi") != null ? ((宣传员费用登记表)model.get("jianyiqi")).get总金额() : 0D;
                    Double chaobannian = model.get("chaobannian") != null ? ((宣传员费用登记表)model.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei = model.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model.get("buchongjieshaofei")).get总金额() : 0D;
                    Double qitafeiyong = model.get("qitafeiyong") != null ? ((宣传员费用登记表)model.get("qitafeiyong")).get总金额() : 0D;
                    Double guanlifeiHeji = (Double)model.get("guanlifeiHeji");

                    //被合并的宣传员
                    String name1 = x.getHebingName();
                    Map<String, Object> model1 = new HashMap<>();
                    model1 = 获取宣传员统计费用(name1,model1, sqlManager);
                    Double jianyiqi1 = model1.get("jianyiqi") != null ? ((宣传员费用登记表)model1.get("jianyiqi")).get总金额() : 0D;
                    Double chaobannian1 = model1.get("chaobannian") != null ? ((宣传员费用登记表)model1.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei1 = model1.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model1.get("buchongjieshaofei")).get总金额() : 0D;
                    Double qitafeiyong1 = model1.get("qitafeiyong") != null ? ((宣传员费用登记表)model1.get("qitafeiyong")).get总金额() : 0D;
                    Double guanlifeiHeji1 = (Double)model1.get("guanlifeiHeji");

                    data.put("model",model);
                    data.put("model1",model1);

                    String excelTemplate = "excelTemplates/管理费费用统计 - 合并车主.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + name + "-" + name1 + ".xls");
                        f.getParentFile().mkdirs();
                        FileOutputStream os = new FileOutputStream(f);
                        JxlsUtils.exportExcel(is,os,data);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    if (StringUtils.equals(name,"曹建涛")) {
                        name = "曹桂勇";
                    }
                    if (StringUtils.equals(name,"张秀春")) {
                        name = "岳良全";
                    }
                    xcyHuizong.put("name",name);
                    xcyHuizong.put("jianyiqi",jianyiqi + jianyiqi1);jianyiqiTotal = jianyiqiTotal + jianyiqi + jianyiqi1;
                    xcyHuizong.put("chaobannian",chaobannian + chaobannian1);chaobannianTotal = chaobannianTotal + chaobannian + chaobannian1;
                    xcyHuizong.put("buchongjieshaofei",buchongjieshaofei + buchongjieshaofei1);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei + buchongjieshaofei1;
                    xcyHuizong.put("qitafeiyong",qitafeiyong + qitafeiyong1);qitafeiyongTotal = qitafeiyongTotal + qitafeiyong + qitafeiyong1;
                    xcyHuizong.put("guanlifeiHeji",guanlifeiHeji + guanlifeiHeji1);guanlifeiHejiTotal = guanlifeiHejiTotal + guanlifeiHeji + guanlifeiHeji1;

                    xcyHuizong.put("heji",jianyiqi + chaobannian + buchongjieshaofei + guanlifeiHeji + qitafeiyong + jianyiqi1 + chaobannian1 + buchongjieshaofei1 + guanlifeiHeji1 + qitafeiyong1);
                    xcyHuizongList.add(xcyHuizong);
                }
            }
            String excelTemplate = "excelTemplates/管理费费用汇总.xls";
            try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                if(is==null) {
                    throw new PlatformException("模板资源不存在："+excelTemplate);
                }
                File f = new File(root + "\\宣传员\\1-汇总（" + dateRange + "）" + xcyList.size() + ".xls");
                f.getParentFile().mkdirs();

                FileOutputStream os = new FileOutputStream(f);
                Map<String, Object> model = new HashMap<>();
                model.put("jianyiqiTotal", jianyiqiTotal);
                model.put("chaobannianTotal", chaobannianTotal);
                model.put("buchongjieshaofeiTotal", buchongjieshaofeiTotal);
                model.put("guanlifeiHejiTotal", guanlifeiHejiTotal);
                model.put("qitafeiyongTotal", qitafeiyongTotal);

                model.put("xcyHuizongList", xcyHuizongList);
                model.put("nowdate", new Date());
                model.put("dateRange",dateRange);
                JxlsUtils.exportExcel(is,os,model);
            } catch (IOException e) {
                throw new PlatformException(e.getMessage());
            }
        }
    }


    public static Map 获取宣传员统计费用(String name, Map model, SQLManager sqlManager) {
        Double jianyiqi = 0D;
        Double chaobannian = 0D;
        Double buchongjieshaofei = 0D;
        Double qitafeiyong = 0D;
        Double guanlifeiHeji = 0D;


        List<路费差额统计> lufeiList = sqlManager.execute(new SQLReady("select * from (select 路费区域,count(*) as count,差额,sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? group by 路费区域,差额) as t order by t.count desc ", new Object[] {name}),路费差额统计.class);
        List<路费差额统计> guanlifeiHejiList = sqlManager.execute(new SQLReady("select sum(差额) as 管理费合计 from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
        List<路费差额统计> guanlifeiZongshu = sqlManager.execute(new SQLReady("select count(*) as count from 路费差额统计 where 宣传员姓名 = ? ", new Object[] {name}),路费差额统计.class);
        if (guanlifeiHejiList != null && guanlifeiHejiList.size() == 1) {
            if (guanlifeiHejiList.get(0).get管理费合计() != null) {
                guanlifeiHeji = guanlifeiHejiList.get(0).get管理费合计();
            }
        }
        model.put("guanlifeiHeji", guanlifeiHeji);
        if (guanlifeiZongshu.get(0).getCount() != 0) {
            model.put("guanlifeiZongshu", guanlifeiZongshu.get(0).getCount());
        }
        try {
            List<宣传员费用登记表> 检疫期费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=30 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}), 宣传员费用登记表.class);
            if (检疫期费用 != null && 检疫期费用.size() == 1) {
                model.put("jianyiqi",检疫期费用.get(0));
                if (检疫期费用.get(0).get总金额() != null) {
                    jianyiqi = 检疫期费用.get(0).get总金额();
                }
            } else if (检疫期费用.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "检疫期费用出错|",true);
            }

            List<宣传员费用登记表> 超半年费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=50 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (超半年费用 != null && 超半年费用.size() == 1) {
                model.put("chaobannian",超半年费用.get(0));
                if (超半年费用.get(0).get总金额() != null) {
                    chaobannian = 超半年费用.get(0).get总金额();
                }
            } else if (超半年费用.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "超半年费用出错|",true);
            }

            List<宣传员费用登记表> 补充介绍费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=40 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (补充介绍费 != null && 补充介绍费.size() == 1) {
                model.put("buchongjieshaofei",补充介绍费.get(0));
                if (补充介绍费.get(0).get总金额() != null) {
                    buchongjieshaofei = 补充介绍费.get(0).get总金额();
                }
            } else if (补充介绍费.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "补充介绍费出错|",true);
            }
            //该项主要针对王利曲堤5人补1人的政策
            List<宣传员费用登记表> 其他费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and (金额 !=30 and 金额!=40 and 金额!=50) and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (其他费用 != null && 其他费用.size() == 1) {
                model.put("qitafeiyong",其他费用.get(0));
                if (其他费用.get(0).get总金额() != null) {
                    qitafeiyong = 其他费用.get(0).get总金额();
                }
            } else if (其他费用.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "其他费用|",true);
            }

            if (lufeiList.size() == 0) {
                路费差额统计 o = new 路费差额统计();
                lufeiList.add(o);
            }
            model.put("qitafeiyongheji",String.valueOf(jianyiqi + chaobannian + buchongjieshaofei + qitafeiyong));
            model.put("lufeiList", lufeiList);
            model.put("name",name);
            model.put("nowdate", new Date());
        } catch (IOException e) {
            e.printStackTrace();
        }

        return model;
    }
}
